BlueCielo Meridian Enterprise 2017 Configuration Guide

Configuring cascading value lists

Cascading value lists are configured by writing a VBScript function for each property that will retrieve only the values related to the value already selected for a different property. The functions are based on the Meridian GetValues methods, which are further described in the BlueCielo Meridian Enterprise VBScript API Reference. The functions are typed as a validation rule for each property.

Note    The following example procedure creates four levels of cascading value lists and assumes that a standard value table named Clients is used to store the data. The example data is shown in the table in Understanding cascading value lists. A standard value query may also be used by specifying the Vault.Query property instead.

To configure cascading value lists:

1. In Configurator, on the Edit menu, select Edit Events. The Meridian Enterprise Script Editor appears, showing the vault’s existing VBScript event procedures, functions, and other code. For information on customizing the Meridian event procedures, see the BlueCielo Meridian Enterprise VBScript API Reference.

2. Scroll to the bottom of the existing code, and create a new function similar to the following code to present a list of values for the first (top) level property (Name) of the cascading value lists:

   Function GetClients GetClients = Vault.Table ("Clients").GetValues (Null, Null, "Name", True, "Name")
   End Function

   This function retrieves all values from the Name column of the Clients table.

3. Click OK to save your changes.
4. Select This expression and type GetClients on the Validation page of the ClientName property.

5. On a new empty line in the Meridian Enterprise Script Editor, create a new function similar to the following code to present a list of values for the next lower (second) level property (ProjectNumber) of the cascading value lists:

   Function GetClientProjects GetClientProjects = Vault.Table ("Clients").GetValues ("Name", Document.ClientName, "Project", True, "Project")
   End Function

   This function retrieves only values from the Project column of the Clients table based on the value selected for Document.ClientName at that moment.

6. Click OK to save your changes.
7. Select This expression and type GetClientProjects on the Validation page of the ProjectNumber property.

8. On a new empty line in the Meridian Enterprise Script Editor, create a new function similar to the following code to present a list of values for the next lower (third) level property (ProjectNumber) of the cascading value lists:

   Function GetClientProjectContracts GetClientProjectContracts = Vault.Table ("Clients").GetValues (Array ("Name", "Project"), Array (Document.ClientName, Document.ProjectNumber), "Contracts", True, "Contracts")
   End Function

   This function retrieves only values from the Contracts column of the Clients table based on the value selected for Document.ClientName and Document.ProjectNumber at that moment.

9. Click OK to save your changes.
10. Select This expression and type GetClientProjectContracts on the Validation page of the ContractNumber property.

11. On a new empty line in the Meridian Enterprise Script Editor, create a new function similar to the following code to present a list of values for the next lower (fourth and final) level property (ContactName) of the cascading value lists:

    Function GetClientProjectContractContacts GetClientProjectContractContacts = Vault.Table ("Clients").GetValues (Array ("Name", "Project", "Contract"), Array (Document.ClientName, Document.ProjectNumber, Document.ContractNumber), "ContactName", True, "ContactName")
    End Function

    This function retrieves only values from the ContractName column of the Clients table based on the value selected for Document.ClientName, Document.ProjectNumber, and Document.ContractNumber at that moment.

12. Click OK to save your changes.
13. Select This expression and type GetClientProjectContractContacts on the Validation page of the ContactNames property.

In all of the preceding example functions, the fourth argument of the GetValues method is True. This is to ensure that the [Distinct] SQL query argument is also True, which ignores duplicate values.

Note    When using column names that contain spaces, you must enclose the column name within square brackets, for example: "[Column name with spaces]".